﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.SqlClient;
using VideoClassLibrary.Model;
using System.Data.Common;

namespace VideoClassLibrary.BusinessLogicLayer
{
   public  class CourseModule
    {
         public DataSet GetCourseListByWhere(int schoolId,string whereSql)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select [ID],[Create_time],[update_time],[SchoolID],[VC_name],[VC_desc] ,trainClassID,vc_content,vc_targetUsers ");
             strSql.Append(" FROM V_course where SchoolID= " + schoolId);

             if (whereSql != "")
             {
                 strSql.Append(whereSql);
             }
             strSql.AppendLine("   order by Create_time desc ");
             Database db = VDataBase.CreateDatabase();

             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

         public DataSet GetCourseListByWhere(int schoolId,int trainClassId, string whereSql)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select [ID],[Create_time],[update_time],[SchoolID],[VC_name],[VC_desc] ,trainClassID,vc_content,vc_targetUsers ");
             strSql.Append(" FROM V_course where SchoolID= " + schoolId + " AND trainClassID = "+ trainClassId);

             if (whereSql != "")
             {
                 strSql.Append(whereSql);
             }
             strSql.AppendLine("   order by Create_time desc ");
             Database db = VDataBase.CreateDatabase();

             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

         /// <summary>
         ///  根据ID获取课程实体
         /// </summary>
         /// <returns></returns>
         public V_course GetCourseModelById(int courseId)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ID,Create_time,update_time,SchoolID,VC_name,VC_desc,trainClassID,vc_content,vc_targetUsers,(select vtc_name from V_trainClass where ID=a.trainClassID ) as trainingClassName  from V_course a ");
             strSql.Append(" where ID=@ID ");
             Database db = VDataBase.CreateDatabase();
             DbCommand command = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(command, "ID", DbType.Int32, courseId);
             DataSet dsResult = db.ExecuteDataSet(command);
             V_course model = null;

             if (dsResult != null && dsResult.Tables.Count > 0 && dsResult.Tables[0].Rows.Count > 0)
             {
                 DataRow dr = dsResult.Tables[0].Rows[0];
                 model = new V_course();
                 model.Create_time = dr["Create_time"] == DBNull.Value ? System.DateTime.Now : System.DateTime.Parse(dr["create_time"].ToString());
                 model.ID = int.Parse(dr["ID"].ToString());
                 model.SchoolID = int.Parse(dr["SchoolID"].ToString());
                 model.TrainClassID = int.Parse(dr["trainClassID"].ToString());
                 model.TrainClassName = dr["trainingClassName"].ToString();
                 model.VC_name = dr["VC_name"].ToString();
                 model.VC_desc = dr["VC_desc"].ToString();
                 model.Vc_content = dr["VC_content"].ToString();
                 model.Vc_targetUsers=dr["VC_targetUsers"].ToString();
                 dsResult = null;
             }
             return model;
         }
         /// <summary>
         ///   添加课程信息
         /// </summary>
         /// <returns></returns>
         public int AddCourseModel(V_course model)
         {
             if (model.TrainClassID == 0)
             {
                 return -1;
             }
             StringBuilder strSql = new StringBuilder();
             strSql.Append("insert into V_course(");
             strSql.Append("Create_time,update_time,SchoolID,trainClassID,VC_name,VC_desc,vc_content,vc_targetUsers)");

             strSql.Append(" values (");
             strSql.Append("@Create_time,@update_time,@SchoolID,@trainClassID,@VC_name,@VC_desc,@vc_content,@vc_targetUsers)");
             strSql.Append(";select @@IDENTITY");
             Database db = VDataBase.CreateDatabase();
             DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(dbCommand, "Create_time", DbType.DateTime, model.Create_time);
             db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.Update_time);
             db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
             db.AddInParameter(dbCommand, "trainClassID", DbType.Int32, model.TrainClassID);
             db.AddInParameter(dbCommand, "VC_name", DbType.AnsiString, model.VC_name);
             db.AddInParameter(dbCommand, "VC_desc", DbType.AnsiString, model.VC_desc);
             db.AddInParameter(dbCommand, "vc_content", DbType.AnsiString, model.Vc_content);
             db.AddInParameter(dbCommand, "vc_targetUsers", DbType.AnsiString, model.Vc_targetUsers);
             int result;
             try
             {
                result=Convert.ToInt32(db.ExecuteScalar(dbCommand).ToString());
             }
             catch
             {
                 result = 0;
             }
             return result;
         }

         /// <summary>
         ///    获取基本的课程信息
         /// </summary>
         /// <returns></returns>
         public DataSet QueryAllCourse(int schoolId,string whereSql)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ID,VC_name,VC_desc,Create_time,update_time ,trainClassID,vc_content,vc_targetUsers from V_course");
             strSql.Append(" where SchoolID=" + schoolId);
             strSql.Append( whereSql);
             Database db = VDataBase.CreateDatabase();
             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

         /// <summary>
         ///获取某个学校下面满足条件的所有课程（不是课程表）
         /// </summary>
         /// <param name="schoolId"></param>
         /// <param name="whereSql"></param>
         /// <returns> add by zhangqian</returns>
         public List<V_course> GetSchoolCourseListByWhere(int schoolId, string whereSql)
         {
             StringBuilder sBuilder = new StringBuilder();
             List<V_course> result = new List<V_course>();
             sBuilder.Append("select * from v_course where SchoolID=@SchoolID  ");
             if (whereSql != "")
             {
                 sBuilder.Append(whereSql);
             }

             Database db = VDataBase.CreateDatabase();
             DbCommand command = db.GetSqlStringCommand(sBuilder.ToString());
             db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
             try
             {
                 DataSet ds = db.ExecuteDataSet(command);
                 if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                 {
                     DataRow dr = null;
                     for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                     {
                         dr = ds.Tables[0].Rows[i];
                         V_course course = new V_course();
                         course.ID = int.Parse(dr["ID"].ToString());
                         course.Create_time = DateTime.Parse(dr["Create_time"].ToString());
                         course.SchoolID = int.Parse(dr["SchoolID"].ToString());
                         course.TrainClassID = int.Parse(dr["trainClassID"].ToString());
                         course.VC_name =dr["VC_name"].ToString();
                         course.VC_desc =dr["VC_desc"].ToString();
                         course.Vc_content = dr["vc_content"].ToString();
                         course.Vc_targetUsers = dr["vc_targetUsers"].ToString();
                         result.Add(course);
                     }
                 }
                 return result;
             }
             catch
             {
                 return result;
             }

         }
         /// <summary>
         ///   学校管理员获取基本的课程信息
         /// </summary>
         /// <returns></returns>
         public DataSet QueryAllCourseBySID(int schoolId)
         {

             StringBuilder strSql = new StringBuilder();
             if (schoolId == 0)
             {
                 strSql.Append("  select a.ID,VC_name,VC_desc,vc_content,vc_targetUsers ,a.Create_time,  a.update_time,a.SchoolID,trainClassID ,b.VTC_name,c.VS_name,VTC_Number  from V_course a join V_trainClass  b on a.trainClassID=b.ID  join V_school c on a.SchoolID=c.ID order by c.VS_name,b.VTC_name ");
             }
             else
             {
                 strSql.Append("select a.ID,VC_name,VC_desc,vc_content,vc_targetUsers ,a.Create_time,  a.update_time,a.SchoolID,trainClassID ,b.VTC_name,c.VS_name,VTC_Number from V_course a join V_trainClass  b on a.trainClassID=b.ID  join V_school c on a.SchoolID=c.ID   where a.SchoolID=" + schoolId + " order by a.Create_time desc");
             }
             Database db = VDataBase.CreateDatabase();
             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }
         /// <summary>
         ///  管理员根据课程名获取基本的课程信息
         /// </summary>
         /// <returns></returns>
         /// 
         public DataSet QueryAllCourseBySIDAndName(int schoolId, string courseName)
         {
             StringBuilder strSql = new StringBuilder();
             if (schoolId == 0)
             {
                 strSql.Append("  select a.ID,VC_name,VC_desc,vc_content,vc_targetUsers ,a.Create_time,  a.update_time,a.SchoolID,trainClassID ,b.VTC_name,c.VS_name ,VTC_Number from V_course a join V_trainClass  b on a.trainClassID=b.ID  join V_school c on a.SchoolID=c.ID where CHARINDEX('" + courseName + "', VC_name)>0 order by c.VS_name,b.VTC_name ");
             }
             else
             {
                 strSql.Append("select a.ID,VC_name,VC_desc,vc_content,vc_targetUsers ,a.Create_time,  a.update_time,a.SchoolID,trainClassID ,b.VTC_name,c.VS_name,VTC_Number  from V_course a join V_trainClass  b on a.trainClassID=b.ID  join V_school c on a.SchoolID=c.ID   where a.SchoolID=" + schoolId + "and CHARINDEX('" + courseName + "', VC_name)>0 order by a.Create_time desc");
             }
             Database db = VDataBase.CreateDatabase();
             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

         #region
         //public DataSet QueryAllCourseBySIDAndName(int schoolId, string courseName)
         //{

         //    StringBuilder strSql = new StringBuilder();
         //    if (schoolId == 0)
         //    { strSql.Append("select [ID],[Create_time],[update_time],[SchoolID],[VC_name],[VC_desc] ,trainClassID from [V_course] where VC_name like '%" + courseName + "%' order by Create_time desc"); }
         //    else { strSql.Append("select ID,Create_time,update_time,SchoolID,VC_name,VC_desc ,trainClassID from V_course where SchoolID=" + schoolId + " and VC_name like '%" + courseName + "%'  order by Create_time desc"); }
         //    Database db = VDataBase.CreateDatabase();
         //    return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         //}
       #endregion

         /// <summary>
         ///  修改课程信息
         /// </summary>
         /// <returns></returns> 
         public int UpdateCourseModel(V_course model)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update V_course set ");
             strSql.Append("update_time=@update_time,");
             strSql.Append("SchoolID=@SchoolID,");
             strSql.Append("trainClassID=@trainClassID,");
             strSql.Append("VC_name=@VC_name,");
             strSql.Append("VC_desc=@VC_desc,");
             strSql.Append("VC_content=@VC_content,");
             strSql.Append("VC_TargetUsers=@VC_TargetUsers,");
             strSql.Append("CourseImage=@CourseImage");
             strSql.Append(" where ID=@ID ");
             Database db = VDataBase.CreateDatabase();
             DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(dbCommand, "ID", DbType.Int32, model.ID);
             db.AddInParameter(dbCommand, "update_time", DbType.DateTime, model.Update_time);
             db.AddInParameter(dbCommand, "SchoolID", DbType.Int32, model.SchoolID);
             db.AddInParameter(dbCommand, "trainClassID", DbType.Int32, model.TrainClassID);
             db.AddInParameter(dbCommand, "VC_name", DbType.AnsiString, model.VC_name);
             db.AddInParameter(dbCommand, "VC_desc", DbType.AnsiString, model.VC_desc);
             db.AddInParameter(dbCommand, "VC_content",DbType.AnsiString,model.Vc_content);
             db.AddInParameter(dbCommand, "VC_TargetUsers", DbType.AnsiString, model.Vc_targetUsers);
             db.AddInParameter(dbCommand, "CourseImage", DbType.AnsiString, model.CourseImage);
             int result = db.ExecuteNonQuery(dbCommand);
             if (result > 0)
             { return 1; }
             else
             { return -1; }

         }

         /// <summary>
         ///  修改课程信息
         /// </summary>
         /// <returns></returns> 
         public int UpdateCoursel(int id,string image)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("update V_course set ");
             strSql.Append("CourseImage=@CourseImage");    
             strSql.Append(" where ID=@ID ");
             Database db = VDataBase.CreateDatabase();
             DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(dbCommand, "ID", DbType.Int32, id);
             db.AddInParameter(dbCommand, "CourseImage", DbType.AnsiString, image);

             int result = db.ExecuteNonQuery(dbCommand);
             if (result > 0)
             { return 1; }
             else
             { return -1; }
         }
   
         /// <summary>
         /// 查找学生要观看的视频文件
         /// </summary>
         /// <param name="schoolId"></param>
         /// <param name="roomId"></param>
         /// <param name="userId"></param>
         /// <param name="courseDate"></param>
         /// <returns>add by zhangqian</returns>
         public DataSet GetUserVideoFile(int courselistID,int schoolId, int roomId, int userId, DateTime courseDate)
         {
    
             StringBuilder strSql = new StringBuilder();

             strSql.Append("select  b.VR_roomName, c.Create_time,c.update_time,c.Is_deleted,VF_filename,VF_filetype,VF_size,VF_desc,VF_timelength  ");
             strSql.Append("  from   V_courselist a  join V_room b on a.RoomID=b.ID  join V_videofile c on b.VR_cameraCode=c.CameraCode  ");
             strSql.Append("  where a.SchoolID=@SchoolID  and b.ID=@RoomID and  DATEDIFF(day,c.Create_time,@CourseDate) =0  and a.ID=@CourseListId ");
             Database db = VDataBase.CreateDatabase();
             DbCommand command = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
             db.AddInParameter(command, "RoomID", DbType.Int32, roomId);
             db.AddInParameter(command, "CourseDate", DbType.DateTime, courseDate);
             db.AddInParameter(command, "CourseListId", DbType.Int32, courselistID);
             DataSet ds = db.ExecuteDataSet(command);
             return ds;
            
         
         }

       

       /// <summary>
       /// 根据课程表ID获取课程的相关信息
       /// </summary>
       /// <param name="schoolId"></param>
       /// <param name="courseListId"></param>
       /// <returns>add by zhangqian </returns>
         public DataSet GetCourseDetailByDestId(int schoolId, int courseListId)
         {
             string sql = " select top 1 b.*   FROM V_courselist a join v_course b on a.courseID=b.ID   where a.SchoolID=@SchoolId and a.ID=@CourseListID";
             Database db = VDataBase.CreateDatabase();
             DbCommand command = db.GetSqlStringCommand(sql);
             db.AddInParameter(command, "SchoolID", DbType.Int32, schoolId);
             db.AddInParameter(command, "CourseListID", DbType.Int32, courseListId);
             return db.ExecuteDataSet(command);

         }

       /// <summary>
       /// 学校id,培训班id 查询课程名称
       /// </summary>
       /// <param name="schoolId">学校</param>
       /// <param name="trainClassID">培训班</param>
       /// <param name="courseName">课程名称</param>
       /// <returns></returns>
         public DataSet QueryCourseBySIDAndName(int schoolId, int trainClassID, string courseName)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("select ID,VC_name from V_course");
             strSql.Append(" where SchoolID=" + schoolId);
             strSql.Append(" and trainClassID=" + trainClassID);
             strSql.Append(" and VC_name like '%" + courseName + "%'");
             Database db = VDataBase.CreateDatabase();
             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

       /// <summary>
       /// 获取课程名称
       /// </summary>
       /// <param name="schoolId"></param>
       /// <returns></returns>
         public DataSet GetAllCoursebySchoolid(int schoolId)
         {

             StringBuilder strSql = new StringBuilder();
             strSql.Append("select top(3) ID,VC_name,VC_desc,Create_time,update_time ,trainClassID,vc_content,vc_targetUsers from V_course");
             strSql.Append(" where SchoolID=" + schoolId);
             Database db = VDataBase.CreateDatabase();
             return db.ExecuteDataSet(CommandType.Text, strSql.ToString());
         }

         /// <summary>
         /// 根据课程表ID删除数据
         /// </summary>
         /// <param name="CID"></param>
         /// <returns></returns>
         public int DeleteCourseList(int cID)
         {
             StringBuilder strSql = new StringBuilder();
             strSql.Append("delete from V_course where ID=@courselistID");
             Database db = VDataBase.CreateDatabase();
             DbCommand command = db.GetSqlStringCommand(strSql.ToString());
             db.AddInParameter(command, "courselistID", DbType.Int32, cID);
             int result = db.ExecuteNonQuery(command);
             if (result > 0)
             { return 1; }
             else
             { return -1; }

         }

      
    }
}
